package com.zyx.jdbcdemo.utils;


import lombok.extern.slf4j.Slf4j;
import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.GenerousBeanProcessor;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.time.Instant;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

/**
 * @author Yaxi.Zhang
 * @since 2021/4/29 13:30
 * desc: Sql工具类
 */
@Slf4j
public class SqlUtil {
    /**
     * Jdbc查询结果并返回
     *
     * @param conn    Jdbc连接
     * @param sql     查询sql语句
     * @param args    占位符参数
     * @param tClass  结果类型
     * @param isCamel 是否需要将下划线转成驼峰命名
     * @param <T>     结果类型的泛型
     * @return 结果列表
     */
    protected static <T> List<T> queryList(Connection conn,
                                           String sql,
                                           Object[] args,
                                           Class<T> tClass,
                                           boolean isCamel) {
        QueryRunner qr = new QueryRunner();
        List<T> queryResult = new ArrayList<>();
        try {
            long start = Instant.now().toEpochMilli();
            // 进行查询
            if (args == null) {
                if (isCamel) {
                    queryResult = qr.query(conn, sql, new BeanListHandler<>(
                            tClass, new BasicRowProcessor(new GenerousBeanProcessor())));
                } else {
                    queryResult = qr.query(conn, sql, new BeanListHandler<>(tClass));
                }
            } else {
                if (isCamel) {
                    queryResult = qr.query(conn, sql, new BeanListHandler<>(
                            tClass, new BasicRowProcessor(new GenerousBeanProcessor())), args);
                } else {
                    queryResult = qr.query(conn, sql, new BeanListHandler<>(tClass), args);
                }
            }
            long end = Instant.now().toEpochMilli();
            log.info("++++++++++++++++++++MySQL查询成功,耗时{}ms", (end - start));
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            // 关闭资源
            try {
                DbUtils.close(conn);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }


        return queryResult;
    }

    /**
     * 将对象的列表插入到数据库中
     *
     * @param conn 对象的连接
     * @param sql  插入语句
     * @param list 对象列表
     * @param <T>  对象的泛型
     */
    protected static <T> void insertList(Connection conn,
                                         String sql,
                                         List<T> list) {
        QueryRunner qr = new QueryRunner();
        try {
            qr.batch(conn, sql, CommonUtil.list2DimObjArray(list));
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            // 关闭资源
            try {
                DbUtils.close(conn);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    /**
     * 更新数据库数据
     *
     * @param conn 对象的连接
     * @param sql  更新语句
     * @param <T>  对象的泛型
     */
    protected static <T> void update(Connection conn,
                                     String sql) {
        QueryRunner qr = new QueryRunner();
        try {
            qr.update(conn, sql);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                DbUtils.close(conn);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }


    /**
     * 获取插入的预编译sql语句
     *
     * @param tblName   插入表的名称
     * @param paramNums 插入表的参数个数
     * @return 插入的预编译sql语句
     */
    public static String getInsertPrepStat(String tblName, int paramNums) {
        StringBuilder sb = new StringBuilder();
        sb.append("INSERT INTO ").append(tblName).append(" VALUES(");
        if (paramNums < 1) {
            throw new RuntimeException("插入表的参数个数不应小于1");
        }
        for (int i = 0; i < paramNums - 1; i++) {
            sb.append("?,");
        }
        sb.append("?)");
        return sb.toString();
    }

    /**
     * 获取数据库的JDBC连接
     *
     * @param propName 配置文件的名称
     * @return 数据库的JDBC连接
     */
    protected static Connection getConnection(String propName) {
        // 读取配置
        Properties connProps = PropUtil.getProperties(propName + ".properties");
        String driverClassName = connProps.getProperty("driverClassName");
        // 加载驱动
        try {
            Class.forName(driverClassName);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            log.error("++++++++++++++++++++ MySQL库驱动加载失败");
        }
        String url = connProps.getProperty("url");
        String username = connProps.getProperty("username");
        String password = connProps.getProperty("password");
        Connection connection = null;
        try {
            connection = DriverManager.getConnection(url, username, password);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            log.error("++++++++++++++++++++ {}连接创建失败", driverClassName);
        }
        return connection;
    }

    /**
     * 设置预编译语句中各个参数的值
     *
     * @param ps  预编译语句
     * @param obj 待插入的对象
     * @param <T> 插入数据的泛型
     */
    public static <T> void setPreparedStatement(PreparedStatement ps, T obj) {
        // 获取当前类中  所有的属性
        Field[] fields = obj.getClass().getDeclaredFields();
        // 跳过的属性计数
        for (int fieldIndex = 0; fieldIndex < fields.length; fieldIndex++) {
            Field field = fields[fieldIndex];
            // 设置私有属性可访问
            field.setAccessible(true);
            try {
                // 获取属性值
                Object o = field.get(obj);
                ps.setObject(fieldIndex + 1, o);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

}